package com.hunan.api.catv.utils;

import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: wangchao
 * @Date: 2020/10/29 10:04
 */
public class JdbcUtils {

    /**
     * 驱动名称
     */
    private static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";

    private static String url = "jdbc:mysql://106.52.218.27:3306/hunan_catv?serverTimezone=Asia/Shanghai&useSSL=false&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true";
    private static String userName = "root";
    private static String passWord = "DiqPK759%pC7";

    private static final Logger log = LoggerFactory.getLogger(JdbcUtils.class);


    /**
     * 定义连接
     */
    private static Connection conn;
    /**
     * 定义STMT
     */
    private static PreparedStatement stmt;
    /**
     * 定义结果集
     */
    private static ResultSet rs;

    /** 初始化加载链接 */
    /*static {
        try {
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection(datasourceProperties.getUrl(), datasourceProperties.getUsername(), datasourceProperties.getPassword());
        } catch (ClassNotFoundException e) {
        } catch (SQLException e) {
        }
    }*/


    /**
     * 获取链接
     */
    public static Connection getConn() {
        try {
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection(url, userName, passWord);
        } catch (ClassNotFoundException e) {
            log.error("驱动加载失败:{}",e );
        } catch (SQLException e) {
            log.error("数据库链接异常:{}",e );
        }
        return conn;
    }

    /**
     * 关闭链接,释放资源
     */
    public static void close() {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }

            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            log.error("资源释放发生异常:{}", e);
        }
    }

    /**
     * 获取指定数据库下所有的表名
     *
     * @param dbNm
     * @return
     */
    public static List<String> getAllTableName(String dbNm) {
        List<String> result = new ArrayList<String>();
        Statement st = null;
        try {
            st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_SCHEMA='" + dbNm + "'");
            while (rs.next()) {
                result.add(rs.getString(1));
            }
        } catch (SQLException e) {
            log.error("{}", e);
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException e) {
                    log.error("{}", e);
                }
            }
            close();
        }
        return result;
    }

    /**
     * 执行SQL返回ResultSet
     */
    public static ResultSet executeSql(String sql, Object... args) {
        try {
            stmt = conn.prepareStatement(sql);
            if (null != args && args.length != 0) {
                for (int i = 0; i < args.length; i++) {
                    stmt.setObject(i + 1, args[i]);
                }
            }

            rs = stmt.executeQuery();
        } catch (SQLException e) {
            log.error("数据查询异常:{}", e);
        }
        return rs;
    }

    /**
     * @title 查询数据结果 , 并封装为对象
     * @author Xingbz
     */
    private static <T> T excuteQuery(Class<T> klass, String sql, Object... args) {
        try {
            rs = executeSql(sql, args);
            ResultSetMetaData metaData = rs.getMetaData();

            Map<String, Object> resultMap = new HashMap<>();
            if (rs.next()) {
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String columnname = metaData.getColumnLabel(i);
                    Object obj = rs.getObject(i);
                    resultMap.put(columnname, obj);
                }
            }
            return JSON.parseObject(JSON.toJSONString(resultMap), klass);
        } catch (Exception e) {
            log.error("数据查询异常:{}", e);
        } finally {
            close();
        }
        return JSON.toJavaObject(new JSONObject(), klass);
    }

    /**
     * @title 查询数据结果 , 并封装为List
     * @author Xingbz
     */
    private static <T> List<T> excuteQueryToList(Class<T> klass, String sql, Object... args) {
        try {
            rs = executeSql(sql, args);
            List<Map<String, String>> resultList = new ArrayList<>();
            Map<String, String> resultMap = new HashMap<>();
            while (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                for (int i = 1; i <= columnCount; i++) {
                    resultMap.put(metaData.getColumnName(i), rs.getString(i));
                }
                resultList.add(resultMap);
            }

            return JSON.parseArray(JSON.toJSONString(resultList), klass);
        } catch (Exception e) {
            log.error("数据查询异常:{}", e);
        } finally {
            close();
        }
        return JSON.parseArray("[]", klass);
    }

    /**
     * 字段类型转换
     */
    public static void recordMappingToMap(String fieldClassName, String fieldName, ResultSet rs, Map fieldValue)
            throws SQLException {
        fieldName = fieldName.toLowerCase();
        // 优先规则：常用类型靠前
        if ("java.lang.String".equals(fieldClassName)) {
            String s = rs.getString(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.lang.Integer".equals(fieldClassName)) {
            int s = rs.getInt(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);// 早期jdk需要包装，jdk1.5后不需要包装
            }
        } else if ("java.lang.Long".equals(fieldClassName)) {
            long s = rs.getLong(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.lang.Boolean".equals(fieldClassName)) {
            boolean s = rs.getBoolean(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.lang.Short".equals(fieldClassName)) {
            short s = rs.getShort(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.lang.Float".equals(fieldClassName)) {
            float s = rs.getFloat(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.lang.Double".equals(fieldClassName)) {
            double s = rs.getDouble(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.sql.Timestamp".equals(fieldClassName)) {
            java.sql.Timestamp s = rs.getTimestamp(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, DateUtil.format(s, DatePattern.NORM_DATETIME_PATTERN));
            }
        } else if ("java.sql.Date".equals(fieldClassName) || "java.util.Date".equals(fieldClassName)) {
            java.util.Date s = rs.getDate(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, DateUtil.format(s, DatePattern.NORM_DATETIME_PATTERN));
            }
        } else if ("java.sql.Time".equals(fieldClassName)) {
            java.sql.Time s = rs.getTime(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, DateUtil.format(s, DatePattern.PURE_TIME_PATTERN));
            }
        } else if ("java.sql.DateTime".equals(fieldClassName) || "java.util.DateTime".equals(fieldClassName)) {
            java.util.Date s = rs.getDate(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, DateUtil.format(s, DatePattern.NORM_DATETIME_PATTERN));
            }
        } else if ("java.lang.Byte".equals(fieldClassName)) {
            byte s = rs.getByte(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, new Byte(s));
            }
        } else if ("[B".equals(fieldClassName) || "byte[]".equals(fieldClassName)) {
            // byte[]出现在SQL Server中
            byte[] s = rs.getBytes(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.math.BigDecimal".equals(fieldClassName)) {
            BigDecimal s = rs.getBigDecimal(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.lang.Object".equals(fieldClassName) || "oracle.sql.STRUCT".equals(fieldClassName)) {
            Object s = rs.getObject(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.sql.Array".equals(fieldClassName) || "oracle.sql.ARRAY".equals(fieldClassName)) {
            java.sql.Array s = rs.getArray(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.sql.Clob".equals(fieldClassName)) {
            java.sql.Clob s = rs.getClob(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else if ("java.sql.Blob".equals(fieldClassName)) {
            java.sql.Blob s = rs.getBlob(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        } else {// 对于其它任何未知类型的处理
            Object s = rs.getObject(fieldName);
            if (rs.wasNull()) {
                fieldValue.put(fieldName, null);
            } else {
                fieldValue.put(fieldName, s);
            }
        }
    }

}
